package com.junzhiit.nengji.shop.dao.impl;

import java.util.Date;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.shop.bo.GoodsAnalysisBO;
import com.junzhiit.nengji.shop.bo.GoodsAnalysisInfoBO;
import com.junzhiit.nengji.shop.dao.GoodsHistoryDAO;
import com.junzhiit.nengji.shop.model.GoodsHistory;
import com.junzhiit.nengji.shop.model.enums.GoodsImagesType;
import com.junzhiit.nengji.shop.model.enums.GoodsStatus;
import com.junzhiit.nengji.shop.model.enums.Is;
import com.junzhiit.nengji.shop.model.enums.UserGoodsType;

@Repository
public class GoodsHistoryDAOImpl extends AbstractBaseDAO<GoodsHistory>implements GoodsHistoryDAO {

	@Override
	public GoodsAnalysisBO getGoodsAnalysis(Date startTime, Date endTime, String systemId, Integer type) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append("select");
		sql.append("	sum(inactive) soldout_goods_count,sum(active) putaway_goods_count,sum(favorite) goods_collections,sum(exposure) exposure_number,");
		sql.append("	sum(view) goods_view_number,sum(visit) visit_user_count,sum(visit_goods) visited_goods_count,sum(cart_number) cart_number,sum(pay_count)/sum(visit) goods_rate ");
		sql.append("from (");
		sql.append("	select ");
		sql.append("		gg.id,gg.inactive,gg.active,gg.favorite,gg.exposure,gg.view,gg.share,gg.visit,gg.visit_goods,gg.cart_number,count(ug.id) pay_count ");
		sql.append("	from (");
		sql.append("	select");
		sql.append("		gg.id,gg.inactive,gg.active,gg.favorite,gg.exposure,gg.view,gg.share,gg.visit,gg.visit_goods,ifnull(sum(c.number),0) cart_number ");
		sql.append("	from (");
		sql.append("		select ");
		sql.append("			gg.id,gg.inactive,gg.active,gg.favorite,ifnull(sum(gah.exposure),0) exposure,ifnull(sum(gah.view),0) view,ifnull(sum(gah.share),0) share,ifnull(sum(gah.visit),0) visit,count(distinct gah.goods_id) visit_goods ");
		sql.append("		from (");
		sql.append("			select ");
		sql.append("				gg.id,gg.inactive,gg.active,count(f.id) favorite ");
		sql.append("			from (");
		sql.append("				select ");
		sql.append("					g.id,case when g.status=0 then 1 else 0 end inactive,case when g.status=1 then 1 else 0 end active ");
		sql.append("				from goods g ");
		sql.append("				where g.is_delete=?");
		params.setInt(IsDelete.NO.toIntValue());
		sql.append("				and g.shop_id in (select s.id from shop s where s.is_delete=?");
		params.setInt(IsDelete.NO.toIntValue());
		if(type==Is.YES.toIntValue()){
			sql.append("			and s.id=?)");
		}
		if(type==Is.NO.toIntValue()){
			sql.append("			and s.chain_id=?)");
		}
		params.setString(systemId);
		sql.append("				) gg ");
		sql.append("			left join favorite f on gg.id=f.goods_id ");
		sql.append("			and left(f.created_time,10) between ? and ? ");
		params.setDate(startTime);
		params.setDate(endTime);
		sql.append("			group by gg.id,gg.inactive,gg.active");
		sql.append("			) gg ");
		sql.append("		left join goods_access_history gah on gg.id=gah.goods_id ");
		sql.append("		and gah.daily between ? and ? ");
		params.setDate(startTime);
		params.setDate(endTime);
		sql.append("		group by gg.id,gg.inactive,gg.active,gg.favorite");
		sql.append("		) gg ");
		sql.append("	left join cart c on gg.id=c.goods_id ");
		sql.append("	and left(c.created_time,10) between ? and ? ");
		params.setDate(startTime);
		params.setDate(endTime);
		sql.append("	group by gg.id,gg.inactive,gg.active,gg.favorite,gg.exposure,gg.view,gg.share,gg.visit");
		sql.append("	) gg ");
		sql.append("left join user_goods ug on gg.id=ug.goods_id ");
		sql.append("and left(ug.created_time,10) between ? and ? ");
		params.setDate(startTime);
		params.setDate(endTime);
		sql.append("group by gg.id,gg.inactive,gg.active,gg.favorite,gg.exposure,gg.view,gg.share,gg.visit,gg.cart_number");
		sql.append(") gg");
		return query(sql.toString(), GoodsAnalysisBO.class, params);
	}

	@Override
	public List<GoodsAnalysisInfoBO> getAllGoodsAnalysis(Integer pageNumber, Integer maxPageRow, Date sTime, Date eTime,
			String systemId, Integer type) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append("select");
		sql.append("	ggi.name,ggi.price,ggi.goods_img,gahe.goods_exposure_number,ogoc.orders_user_count,ogoc.orders_goods_number,ugc.pay_user_count,ugc.pay_goods_number,");
		sql.append("	gahe.visit_user_count,gahe.goods_view_number,fc.goods_collections,cc.cart_goods_number,ugc.pay_user_count/gahe.visit_user_count single_rate ");
		sql.append("from (");
		sql.append("	select ");
		sql.append("		g.id,g.name,gi.file_name goods_img, ");
		sql.append("		(case g.is_open_sku when ? then g.price when ? then (select min(sc.price) from sku_content sc where sc.is_delete=? and sc.goods_id=g.id) end) price");
		params.setInt(Is.NO.toIntValue());
		params.setInt(Is.YES.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		sql.append("	from goods g ");
		sql.append("	inner join goods_images gi on g.id=gi.goods_id ");
		sql.append("	where g.status=? and g.created_time between ? and ? and gi.type=?");
		params.setInt(GoodsStatus.ACTIVE.toIntStatus());
		params.setDate(sTime);
		params.setDate(eTime);
		params.setInt(GoodsImagesType.CARD.toIntStatus());
		sql.append("	and g.shop_id in (select s.id from shop s where s.is_delete=?");
		params.setInt(IsDelete.NO.toIntValue());
		if (type == Is.YES.toIntValue()) {
			sql.append(" and s.id=?)");
		}
		if(type==Is.NO.toIntValue()){
			sql.append(" and s.chain_id=?)");
		}
		params.setString(systemId);
		sql.append("	) ggi");
		sql.append("	left join (");
		sql.append("		select ");
		sql.append("			gah.goods_id,sum(gah.exposure) goods_exposure_number,sum(gah.visit) visit_user_count,sum(gah.view) goods_view_number ");
		sql.append("		from goods_access_history gah");
		sql.append("		where gah.is_delete=?");
		params.setInt(IsDelete.NO.toIntValue());
		sql.append("		group by gah.goods_id");
		sql.append("		) gahe ");
		sql.append("	on ggi.id=gahe.goods_id ");
		sql.append("	left join (");
		sql.append("		select ");
		sql.append("			og.goods_id,count(distinct o.user_id) orders_user_count,sum(og.number) orders_goods_number ");
		sql.append("		from orders_goods og ");
		sql.append("		inner join orders o on og.order_id=o.id ");
		sql.append("		where og.is_delete=? ");
		params.setInt(IsDelete.NO.toIntValue());
		sql.append("		group by og.goods_id");
		sql.append("		) ogoc ");
		sql.append("	on ggi.id=ogoc.goods_id ");
		sql.append("	left join (");
		sql.append("		select ");
		sql.append("			ug.goods_id,count(distinct ug.user_id) pay_user_count,sum(ug.number) pay_goods_number ");
		sql.append("		from user_goods ug ");
		sql.append("		where ug.is_delete=? and ug.type=? ");
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(UserGoodsType.BUY.toIntValue());
		sql.append("		group by ug.goods_id");
		sql.append("		) ugc");
		sql.append("	on ggi.id=ugc.goods_id ");
		sql.append("	left join (");
		sql.append("		select ");
		sql.append("			f.goods_id,count(distinct f.user_id) goods_collections ");
		sql.append("		from favorite f ");
		sql.append("		where f.is_delete=?");
		params.setInt(IsDelete.NO.toIntValue());
		sql.append("		group by f.goods_id");
		sql.append("		) fc ");
		sql.append("	on ggi.id=fc.goods_id ");
		sql.append("	left join (");
		sql.append("		select ");
		sql.append("			c.goods_id,sum(c.number) cart_goods_number ");
		sql.append("		from cart c ");
		sql.append("		where c.is_delete=? ");
		params.setInt(IsDelete.NO.toIntValue());
		sql.append("		group by c.goods_id");
		sql.append("		) cc ");
		sql.append("	on ggi.id=cc.goods_id");
		if (pageNumber != null && maxPageRow != null) {
			return queryForPagingList(sql.toString(), GoodsAnalysisInfoBO.class, params, pageNumber, maxPageRow);
		} else {
			return queryForList(sql.toString(), GoodsAnalysisInfoBO.class, params);
		}
	}

	@Override
	public Integer getAllChainGoodsTotalCount(Date sTime, Date eTime, String systemId, Integer type) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append("select");
		sql.append("	count(ggi.id)");
		sql.append("from (");
		sql.append("	select ");
		sql.append("		g.id,g.name,gi.file_name goods_img, ");
		sql.append("		(case g.is_open_sku when ? then g.price when ? then (select min(sc.price) from sku_content sc where sc.is_delete=? and sc.goods_id=g.id) end) price");
		params.setInt(Is.NO.toIntValue());
		params.setInt(Is.YES.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		sql.append("	from goods g ");
		sql.append("	inner join goods_images gi on g.id=gi.goods_id ");
		sql.append("	where g.status=? and g.created_time between ? and ? and gi.type=?");
		params.setInt(GoodsStatus.ACTIVE.toIntStatus());
		params.setDate(sTime);
		params.setDate(eTime);
		params.setInt(GoodsImagesType.CARD.toIntStatus());
		sql.append("	and g.shop_id in (select s.id from shop s where s.is_delete=?");
		params.setInt(IsDelete.NO.toIntValue());
		if (type == Is.YES.toIntValue()) {
			sql.append(" and s.id=?)");
		}
		if(type==Is.NO.toIntValue()){
			sql.append(" and s.chain_id=?)");
		}
		params.setString(systemId);
		sql.append("	) ggi");
		sql.append("	left join (");
		sql.append("		select ");
		sql.append("			gah.goods_id,sum(gah.exposure) goods_exposure_number,sum(gah.visit) visit_user_count,sum(gah.view) goods_view_number ");
		sql.append("		from goods_access_history gah");
		sql.append("		where gah.is_delete=?");
		params.setInt(IsDelete.NO.toIntValue());
		sql.append("		group by gah.goods_id");
		sql.append("		) gahe ");
		sql.append("	on ggi.id=gahe.goods_id ");
		sql.append("	left join (");
		sql.append("		select ");
		sql.append("			og.goods_id,count(distinct o.user_id) orders_user_count,sum(og.number) orders_goods_number ");
		sql.append("		from orders_goods og ");
		sql.append("		inner join orders o on og.order_id=o.id ");
		sql.append("		where og.is_delete=? ");
		params.setInt(IsDelete.NO.toIntValue());
		sql.append("		group by og.goods_id");
		sql.append("		) ogoc ");
		sql.append("	on ggi.id=ogoc.goods_id ");
		sql.append("	left join (");
		sql.append("		select ");
		sql.append("			ug.goods_id,count(distinct ug.user_id) pay_user_count,sum(ug.number) pay_goods_number ");
		sql.append("		from user_goods ug ");
		sql.append("		where ug.is_delete=? and ug.type=? ");
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(UserGoodsType.BUY.toIntValue());
		sql.append("		group by ug.goods_id");
		sql.append("		) ugc");
		sql.append("	on ggi.id=ugc.goods_id ");
		sql.append("	left join (");
		sql.append("		select ");
		sql.append("			f.goods_id,count(distinct f.user_id) goods_collections ");
		sql.append("		from favorite f ");
		sql.append("		where f.is_delete=?");
		params.setInt(IsDelete.NO.toIntValue());
		sql.append("		group by f.goods_id");
		sql.append("		) fc ");
		sql.append("	on ggi.id=fc.goods_id ");
		sql.append("	left join (");
		sql.append("		select ");
		sql.append("			c.goods_id,sum(c.number) cart_goods_number ");
		sql.append("		from cart c ");
		sql.append("		where c.is_delete=? ");
		params.setInt(IsDelete.NO.toIntValue());
		sql.append("		group by c.goods_id");
		sql.append("		) cc ");
		sql.append("	on ggi.id=cc.goods_id");
		return queryForInt(sql.toString(), params);
	}
}
